SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO






CREATE PROC [dbo].[SP_TXEXTR_BANC_Q01]
/*---------------------------------------------------------------------*/
/*----- Empresa          : OFISIS S.A.                            -----*/
/*----- Cliente          : OFISIS S.A.                         	  -----*/
/*----- Sistema          : Contabilidad                           -----*/
/*----- Modulo           : Empresa                                -----*/
/*----- Programa         : Reporte de Conciliacion Bancaria       -----*/
/*----- Nombre           : SP_TXEXTR_BANC_Q01                	  -----*/
/*----- Script           : TXEXTR_BANC                            -----*/
/*----- Desarrollado por : Martin Garcia Farfan                   -----*/
/*----- Fecha            : 23/10/2002                             -----*/
/*----- Base de Datos    : MS SQL Server                          -----*/
/*----- Version BD       : 7                                      -----*/
/*----- Invoca a SP      :                                        -----*/
/*---------------------------------------------------------------------*/
/*---------------------------------------------------------------------*/
/*----- Drop Proc SP_TXEXTR_BANC_Q01                              -----*/
/*----- Grant Execute On SP_TXEXTR_BANC_Q01 To PUBLIC             -----*/
/*----- SP_TXEXTR_BANC_Q01 '01',1,1999,3, '10401'             	  -----*/
/*---------------------------------------------------------------------*/
@ISCO_EMPR         TD_VC_002,
@INNU_CNTB_EMPR    TD_IN_001,
@INNU_ANNO         TD_IN_004_ANNO,
@INNU_MESE         TD_IN_002_MESE,
@ISCO_CNTA_EMPR    TD_VC_020
As
Declare
 @CDFE_DOCU          TD_DT_001,
 @CSTI_DOCU          TD_VC_003,
 @CSNU_DOCU          TD_VC_020,
 @CSCO_MONE          TD_VC_003,
 @CSTI_OPER          TD_VC_003,
 @VSCO_OPRC          TD_VC_003,
 @CNIM_MVTO_DOCU     TD_NU_016_004,
 @VSCO_OPRC_DIFE     TD_VC_003,
 @VSTI_REPO          TD_VC_001
 
 Create Table #TWEXTR_BANC_Q01_1
 (TI_REPO          char(01)     NULL,
  FE_DOCU          datetime     NULL,
  TI_DOCU          char(03)     NULL,
  NU_DOCU          varchar(20)  NULL,
  IM_MVTO_DOCU     Numeric(16,4)NULL,
  TI_REPO_0001     char(01) NULL )

 Select @VSCO_OPRC = ID_DIFE
 From TTCNTB_EMPR
 Where CO_EMPR = @ISCO_EMPR
 and NU_CNTB_EMPR = @INNU_CNTB_EMPR

 Select @VSCO_OPRC_DIFE = CO_OPRC_CNTB
 from TTIDEN_OPER
 where CO_EMPR = @ISCO_EMPR
 and   NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and   ID_OPRC = @VSCO_OPRC

 Declare CU_TXEXTR_BANC_Q01_1 cursor
 for
  Select FE_DOCU, TI_DOCU, NU_DOCU, CO_MONE, TI_OPER, Isnull(sum(Isnull(IM_MVTO_DOCU, 0)), 0)
   from  TXMVTO_BANC
   where CO_EMPR      = @ISCO_EMPR 
   And   NU_CNTB_EMPR = @INNU_CNTB_EMPR  
   And   NU_ANNO = @INNU_ANNO 
   And   NU_MESE <= @INNU_MESE 
   And   CO_CNTA_EMPR = @ISCO_CNTA_EMPR 
   And   (ST_FLAG = 'N' OR (NU_ANNO_CONS= @INNU_ANNO AND NU_MESE_CONS > @INNU_MESE))
   group by FE_DOCU, TI_DOCU, NU_DOCU, CO_MONE, TI_OPER
   order by FE_DOCU, TI_DOCU, NU_DOCU

 Declare CU_TXEXTR_BANC_Q01_2 cursor
 for
   Select FE_DOCU, TI_DOCU, NU_DOCU, TI_OPER, Isnull(sum(Isnull(IM_MVTO_DOCU, 0)), 0)
    from  TXEXTR_BANC
    where CO_EMPR  = @ISCO_EMPR 
    And   NU_CNTB_EMPR = @INNU_CNTB_EMPR  
    And   NU_ANNO = @INNU_ANNO 
    And   NU_MESE <= @INNU_MESE 
    And   CO_CNTA_EMPR = @ISCO_CNTA_EMPR 
    And   (ST_FLAG = 'N' OR (NU_ANNO_CONS= @INNU_ANNO AND NU_MESE_CONS > @INNU_MESE))
   Group by FE_DOCU, TI_DOCU, NU_DOCU, TI_OPER
   Order by FE_DOCU, TI_DOCU, NU_DOCU

/*---- los movimientos internos ---*/
 open CU_TXEXTR_BANC_Q01_1
 Fetch CU_TXEXTR_BANC_Q01_1 into @CDFE_DOCU, @CSTI_DOCU, @CSNU_DOCU, @CSCO_MONE, @CSTI_OPER, 
       @CNIM_MVTO_DOCU
While @@Fetch_Status = 0
 Begin
     if @CSTI_OPER = 'CAR'
       Select @VSTI_REPO = '1'
     else
       Select @VSTI_REPO = '2'
   
     if @VSTI_REPO = '1' 
        insert into #TWEXTR_BANC_Q01_1
         values(@VSTI_REPO, @CDFE_DOCU, @CSTI_DOCU, @CSNU_DOCU, @CNIM_MVTO_DOCU * -1,'1')
     Else
        insert into #TWEXTR_BANC_Q01_1
         values(@VSTI_REPO, @CDFE_DOCU, @CSTI_DOCU, @CSNU_DOCU, @CNIM_MVTO_DOCU,'1')

 Fetch CU_TXEXTR_BANC_Q01_1 into @CDFE_DOCU, @CSTI_DOCU, @CSNU_DOCU, @CSCO_MONE, @CSTI_OPER, 
       @CNIM_MVTO_DOCU
 End
 close CU_TXEXTR_BANC_Q01_1
 deallocate CU_TXEXTR_BANC_Q01_1

/*----- los movimientos del extracto ---*/
 open CU_TXEXTR_BANC_Q01_2
 Fetch CU_TXEXTR_BANC_Q01_2 into  @CDFE_DOCU, @CSTI_DOCU, @CSNU_DOCU, @CSTI_OPER, 
       @CNIM_MVTO_DOCU
 
 While @@Fetch_Status = 0
  Begin
    If @CSTI_OPER = 'CAR'
      Select @VSTI_REPO = '2'
    Else
      Select @VSTI_REPO = '1'
   
    If @VSTI_REPO = '1' 
       insert into #TWEXTR_BANC_Q01_1
        values(@VSTI_REPO, @CDFE_DOCU, @CSTI_DOCU, @CSNU_DOCU, @CNIM_MVTO_DOCU * -1, '2')
    Else
       insert into #TWEXTR_BANC_Q01_1
        values(@VSTI_REPO, @CDFE_DOCU, @CSTI_DOCU, @CSNU_DOCU, @CNIM_MVTO_DOCU,'2')

  Fetch CU_TXEXTR_BANC_Q01_2 into  @CDFE_DOCU, @CSTI_DOCU, @CSNU_DOCU,
        @CSTI_OPER, @CNIM_MVTO_DOCU
  End
 close CU_TXEXTR_BANC_Q01_2
 deallocate CU_TXEXTR_BANC_Q01_2

 Select TI_REPO, CONVERT(VARCHAR(10),FE_DOCU, 103), TI_DOCU, NU_DOCU, IM_MVTO_DOCU,TI_REPO_0001
 From #TWEXTR_BANC_Q01_1
 Order By TI_REPO Desc, FE_DOCU
return
/*--------------------------------- Fin ------------------------------------*/










GO
